package com.jlastu.dlxy_androidapp.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

    private static final String TAG = "DBHelper";
    private static final String DATABASE_NAME = "znb.db";// 数据库名
    private static final int DATABASE_VERSION = 1;// 数据库版本号
    private static final String TABLE_NAME_GOODS = "goods"; //商品表
    private static final String TABLE_NAME_USER = "user"; //用户表
    private static final String TABLE_NAME_ORDER = "order2"; // 订单表 order是关键字
    private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
    private static final String TABLE_NAME_PHOTO = "photo";
    private static final String TABLE_NAME_SCHOOL = "school";
    private static final String TABLE_NAME_SCHOOLPHOTO = "schoolphoto";
    private static final String TABLE_NAME_ADDRESS = "address";
    private static final String TABLE_NAME_STORE = "store";
    private static final String TABLE_NAME_CART = "cart";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //数据库创建时被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }

    //数据库升级
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) { // 数据库 1 -> 2 升级
            // 创建设置表
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (keycolumn text primary key,valuecolumn text)");
        }
    }

    /**
     * 初始化数据表
     *
     * @param db 数据库
     */
    private void initTable(SQLiteDatabase db) {
        // 创建商品表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_GOODS + " (" +
                "_id integer primary key autoincrement," +
                "store_id integer," +
                "goods_name text," +
                "goods_price double," +
                "goods_unit text," +
                "goods_browse integer," +
                "goods_classify text," +
                "goods_message text," +
                "FOREIGN KEY (store_id) REFERENCES store(_id))");
        // 创建用户表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER + " (" +
                "_id integer primary key autoincrement," +
                "user_name text," +
                "user_password text," +
                "user_phone text," +
                "user_age text," +
                "user_headimg BLOB)");
        // 创建订单表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ORDER + " (" +
                "_id integer primary key autoincrement," +
                "order_name text," +
                "order_number  bigint," +
                "user_id  integer," +
                "FOREIGN KEY (user_id) REFERENCES user(_id))");
        // 创建图片表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_PHOTO + " (" +
                "_id integer primary key autoincrement," +
                "goods_id  integer," +
                "image BLOB," +
                "FOREIGN KEY (goods_id) REFERENCES goods(_id))");

        //创建学堂文章表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SCHOOL + "(" +
                "_id integer primary key autoincrement," +
                "school_name text," +
                "school_classify text," +
                "school_know text)");
//创建学堂图片
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SCHOOLPHOTO + " (" +
                "_id integer primary key autoincrement," +
                "school_id  integer," +
                "image BLOB," +
                "FOREIGN KEY (school_id) REFERENCES school(_id))");
//创建收货地址表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_ADDRESS + " (" +
                "_id integer primary key autoincrement," +
                "address_id  integer," +
                "address text," +
                "FOREIGN KEY (address_id) REFERENCES user(_id))");
//创建店铺表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_STORE + " (" +
                "_id integer primary key autoincrement," +
                "user_id  integer," +
                "store_name text," +
                "store_address text," +
                "store_headimg BLOB," +
                "FOREIGN KEY (user_id) REFERENCES user(_id))");
//        创建购物车表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_CART + " (" +
                "_id integer primary key autoincrement," +
                "user_id  integer," +
                "goods_id text," +
                "goods_name," +
                "goods_price double," +
                "goods_unit text," +
                "FOREIGN KEY (user_id) REFERENCES user(_id))");
    }

    /**
     * 初始化数据库内部模拟数据(仅在第一次安装时执行)
     *
     * @param db 数据库
     */
    private void initData(SQLiteDatabase db) {
//      db.execSQL("insert into " + TABLE_NAME_GOODS +
//              " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
//              "values" +
//              "('四川峨眉山黄瓜', 1.23, '斤', '123', '来自四川雪山之巅的黄瓜。')");
//      db.execSQL("insert into " + TABLE_NAME_GOODS +
//              " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
//              "values" +
//              "('东北大米', 3.33, '斤', '444', '来自东北的大米，煮饭特别香。')");
//      db.execSQL("insert into " + TABLE_NAME_GOODS +
//              " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
//              "values" +
//              "('海南香蕉', 9.99, '斤', '213', '海南香蕉，特别好吃')");
//      db.execSQL("insert into " + TABLE_NAME_GOODS +
//              " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
//              "values" +
//              "('新疆又香又甜的哈密瓜', 5.99, '斤', '333', '又大又甜，特别好吃')");
//      db.execSQL("insert into " + TABLE_NAME_GOODS +
//              " (goods_name , goods_price, goods_unit, goods_browse, goods_message) " +
//              "values" +
//              "('江苏黄瓜', 1.12, '斤', '223', '来自江苏的黄瓜。')");

//        db.execSQL("insert into " + TABLE_NAME_USER +
//                " (user_name , user_password) " +
//                "values" +
//                "('1', '1')");
//        db.execSQL("insert into " + TABLE_NAME_USER +
//                " (user_name , user_password) " +
//                "values" +
//                "('2', '2')");
//        db.execSQL("insert into " + TABLE_NAME_USER +
//                " (user_name , user_password) " +
//                "values" +
//                "('3', '3')");
    }
}
